package we.member;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class conDBmember {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;

	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/shoponline";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "12345");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	// thuc hien cac cau lenh cho data sanpham
	public ArrayList<member> getListMember() throws Exception {
		ArrayList<member> lst = new ArrayList<member>();
		String strSQL = "select * from members";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				int id=Integer.parseInt(rs.getString("id"));
				String fname = rs.getString("first_name");
				String lname = rs.getString("last_name");
				String mail = rs.getString("email");
				String un = rs.getString("uname");
				String pss = rs.getString("pass");
				String rd = rs.getString("regdate");
				member mb = new member(id, fname, lname, mail,un,pss,rd);
				lst.add(mb);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}

	public boolean insertNewMB(member mb) throws Exception {
		String sql = "insert into members values(?,?,?,?,?,?,?)";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, mb.getId());
		pst.setString(2, mb.getFirst_name());
		pst.setString(3, mb.getLast_name());
		pst.setString(4, mb.getEmail());
		pst.setString(5, mb.getUname());
		pst.setString(6, mb.getPass());		
		return pst.executeUpdate() > 0;

	}

	public boolean deleteMember(int id) throws Exception {
		String sql = "delete from members where id=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, id);
		return pst.executeUpdate() > 0;
	}

	/**
	 * Used to execute the Insert, Update, Delete statement
	 * 
	 * @param strSQL
	 *            Query VD: Insert into TableName values ('??','??')
	 * @return The number of lines affected by the command
	 */
	public int executeUpdate(String strSQL) throws Exception {
		int result = 0;
		try {
			result = getStatement().executeUpdate(strSQL);
		} catch (Exception ex) {
			throw new Exception(ex.getMessage() + " Error at: " + strSQL);
		} finally {
			this.closeConnet();
		}
		return result;
	}

	public member getMemberID(int id) throws Exception {
		String sql = "select * from members where id=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		member mb = null;
		if (rs.next()) {
			int mid=Integer.parseInt(rs.getString("id"));
			String fname = rs.getString("first_name");
			String lname = rs.getString("last_name");
			String mail = rs.getString("email");
			String un = rs.getString("uname");
			String pss = rs.getString("pass");
			String rd = rs.getString("regdate");
			 mb = new member(mid, fname, lname, mail,un,pss,rd);
			
		}
		return mb;
	}

	public boolean UpdateProduct(int id, member newmb) throws Exception {
		String sql = "update members set first_name=?, last_name=?, email=?,uname=?,pass=?,regdate? where id=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, newmb.getFirst_name());
		pst.setString(2, newmb.getLast_name());
		pst.setString(3, newmb.getEmail());
		pst.setString(4, newmb.getUname());
		pst.setString(5, newmb.getPass());
		pst.setString(6, newmb.getRedate());
		pst.setInt(7, newmb.getId());
		return pst.executeUpdate() > 0;
	}

	
	/**
	 * A method to close the connection.
	 * 
	 * @throws SQLException
	 */
	public void closeConnet() throws SQLException {
		if (rs != null && !rs.isClosed())
			rs.close();
		if (stmt != null)
			stmt.close();
		if (connect != null)
			connect.close();
	}
}
